package core;

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.List;
import java.util.Random;

import util.Constants;
import util.DBConn;
import util.FileAddrUtil;

/**
 * Generate queries from the pre-defined space and proportion write queries to
 * file generatev1: to generate a rectangle with a junction as centroid <br>
 * 
 * @author Xiaohui
 * 
 */
public class QueryGen implements Constants {

	// double[] percents = { 0.01, 0.02, 0.04, 0.06, 0.08, 0.1 };
	static long seed = 1234536;
	static long seedTime = 837084736;
	static NumberFormat timeFormatter = new DecimalFormat("#.##");

	/**
	 * produce 100 queries in the queryFile with specified spatial range and
	 * temporal range for oldenburg road network.
	 * 
	 * @param spatialPortion
	 *            : sampled spatial range
	 * @param temporalPortion
	 *            : sampled temporal range
	 * @param queryFile
	 *            : the generated file
	 * 
	 * 
	 */
	public static void genQueryFileForOlden(double spatialPortion,
			double temporalPortion, int totalTimeLength, String queryFile)
			throws Exception {
		int counter = 1;
		BufferedWriter out = new BufferedWriter(new FileWriter(queryFile));
		Random rand = new Random(seed);
		while (counter <= 100) {
			double x1 = (oldmaxx - oldminx) * rand.nextDouble() + oldminx;
			double y1 = (oldmaxy - oldminy) * rand.nextDouble() + oldminy;
			double x2 = x1 + Math.sqrt(spatialPortion) * (oldmaxx - oldminx);
			double y2 = y1 + Math.sqrt(spatialPortion) * (oldmaxy - oldminy);

			int startTime = rand.nextInt(totalTimeLength);
			int endTime = (int) (startTime + temporalPortion * totalTimeLength) + 1;

//			if (queryDB(x1, y1, x2, y2, startTime, endTime) > 0) {
//				out.write(2 + " " + counter + " " + x1 + " " + y1 + " " + x2
//						+ " " + y2 + " " + startTime + " " + endTime);
//				out.newLine();
//				counter++;
//			}
			out.write(2 + " " + counter + " " + x1 + " " + y1 + " " + x2
					+ " " + y2 + " " + startTime + " " + endTime);
			out.newLine();
			counter++;
		}
		out.close();
	}

	/**
	 * make sure queries return some objects
	 * 
	 * @param queryFile
	 */
	static void validateQuery(String queryFile) throws Exception {
		List<QueryParser> parsers = QueryFileReader.getParsers(queryFile);
		for (QueryParser p : parsers) {
			if (queryDB(p) == 0) {
				System.out.print(p.getQid());
				System.out.print(", ");
			}
		}
		System.out.println();
	}

	static private int queryDB(QueryParser p) throws Exception {
		Connection conn = DBConn.getConn();
		Statement st = conn.createStatement();
		String sql = "select count(distinct(id)) from " + rawDataTableName
				+ " where x_coord >" + p.getQx1() + " and y_coord>"
				+ p.getQy1() + " and x_coord<" + p.getQx2() + " and y_coord<"
				+ p.getQy2() + " and curr_time>" + p.getStartTime()
				+ " and curr_time<" + p.getEndTime();
		// System.out.println(sql);
		ResultSet rs = st.executeQuery(sql);
		int count = 0;
		while (rs.next()) {
			count = rs.getInt(1);
		}
		rs.close();
		st.close();
		// System.out.println(count);
		// System.out.println("=========");
		return count;
	}

	/**
	 * get the size a query's result set
	 * 
	 * @param x1
	 * @param y1
	 * @param x2
	 * @param y2
	 * @param startTime
	 * @param endTime
	 * @return
	 * @throws Exception
	 */
	static int queryDB(double x1, double y1, double x2, double y2,
			double startTime, double endTime) throws Exception {
		Connection conn = DBConn.getConn();
		Statement st = conn.createStatement();
		String sql = "select count(distinct(id)) from " + rawDataTableName
				+ " where x_coord >" + x1 + " and y_coord>" + y1
				+ " and x_coord<" + x2 + " and y_coord<" + y2
				+ " and curr_time>" + startTime + " and curr_time<" + endTime;
		// System.out.println(sql);
		ResultSet rs = st.executeQuery(sql);
		int count = 0;
		while (rs.next()) {
			count = rs.getInt(1);
		}
		rs.close();
		st.close();
		// System.out.println(count);
		// System.out.println("=========");
		return count;
	}

	public void generatev1(String destFileName) throws Exception {
		double[] percents = { 0.08 };
		Connection conn = DBConn.getConn();
		Statement st = conn.createStatement();
		String sqlminmax = "select min(id), max(id) from " + loc2idTableName;
		// String sqlmax = "select max(id) from " + loc2idTableName;

		// for verification purpose
		String sqlFile = FileAddrUtil.nameAppend(destFileName, "sql");

		int min = 0;
		int max = 0;
		// get ids
		ResultSet rs = st.executeQuery(sqlminmax);
		if (rs.next()) {
			min = rs.getInt(1);
			max = rs.getInt(2);
		} else {
			System.err.println("Error in Generating Query for Min and Max");
			System.exit(0);
		}

		// temporal
		// String sqltimeminmax =
		// "select min(curr_time), max(curr_time) from "+rawDataTableName;
		int timemin = Integer.MAX_VALUE;
		int timemax = Integer.MIN_VALUE;
		// rs = st.executeQuery(sqltimeminmax);
		// if (rs.next()) {
		// timemin = rs.getInt(1);
		// timemax = rs.getInt(2);
		// }
		rs.close();

		Random rand = new Random(seed);
		PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM "
				+ loc2idTableName + " WHERE id = ?");
		int counter = 1;
		BufferedWriter out = new BufferedWriter(new FileWriter(destFileName));
		BufferedWriter sqlout = new BufferedWriter(new FileWriter(sqlFile));
		System.out.println("Writing queries...");
		int id_prev = 0; // to remember last id;
		// generate ten queries
		// for (int j = 0; j < 10; j++) {
		for (int i = 0; i < percents.length; i++) {
			double proportion = rand.nextDouble();
			// convert into <min, max>
			int id = (int) (proportion * (max - min) + min);
			while (id == id_prev) {
				proportion = rand.nextDouble();
				id = (int) (proportion * (max - min) + min);
			}
			id_prev = id;

			pstmt.setInt(1, id);
			ResultSet t1 = pstmt.executeQuery();
			if (t1.next()) {
				double xloc = t1.getDouble("xloc");
				double yloc = t1.getDouble("yloc");

				// System.out.println("corrds: " + xloc + ", " + yloc);
				double extend = percents[i] / 2;
				double xlower = (xloc - extend
						* (destSpace.getMaxX() - destSpace.getMinX()));
				double xupper = (xloc + extend
						* (destSpace.getMaxX() - destSpace.getMinX()));
				double ylower = yloc - extend
						* (destSpace.getMaxY() - destSpace.getMinY());
				double yupper = yloc + extend
						* (destSpace.getMaxY() - destSpace.getMinY());

				String sqlline = "select * from " + rawDataTableName
						+ " where x_nextNode >" + xlower + " and y_nextNode>"
						+ ylower + " and x_nextNode<" + xupper
						+ " and y_nextNode<" + yupper;
				String sqlline1 = "select * from " + loc2idTableName
						+ " where xloc >" + xlower + " and yloc>" + ylower
						+ " and xloc<" + xupper + " and yloc<" + yupper;

				String sqltimeminmax = "select min(curr_time), max(curr_time) from "
						+ rawDataTableName
						+ " where x_nextNode >"
						+ xlower
						+ " and y_nextNode>"
						+ ylower
						+ " and x_nextNode<"
						+ xupper + " and y_nextNode<" + yupper;

				ResultSet temprs = st.executeQuery(sqltimeminmax);
				if (temprs.next()) {
					if (temprs.getInt(1) < timemin) {
						timemin = temprs.getInt(1);
					}
					if (temprs.getInt(2) > timemax) {
						timemax = temprs.getInt(2);
					}
				} else {
					System.err
							.println("Error in Generating Query for TimeMin and TimeMax");
					System.exit(0);
				}

				double[] tempInterval = generateHalfInterval(timemin, timemax);
				String line = query + " " + counter + " " + xlower + " "
						+ ylower + " " + xupper + " " + yupper + " "
						+ tempInterval[0] + " " + tempInterval[1];
				String verifySqlRawData = sqlline + " AND " + "curr_time>"
						+ tempInterval[0] + " AND curr_time<" + tempInterval[1];
				// String verifySqlLocId = sqlline1 + " AND " + "curr_time>"
				// + tempInterval[0] + " AND curr_time<" + tempInterval[1];
				sqlout.write(verifySqlRawData);
				sqlout.newLine();
				sqlout.write(sqlline1);
				sqlout.newLine();

				out.write(line);
				out.newLine();
			}
			t1.close();
			counter++;
		}
		// }
		st.close();
		out.close();
		sqlout.close();
		// System.out.println("Done!");
	}

	/**
	 * Generate interval bigger than 1/2 of the original length
	 * 
	 * @param min
	 * @param max
	 * @return
	 */
	private double[] generateRandomInterval(int min, int max) {
		double midval = (max + min) / 2;
		Random r = new Random();
		double firstval = Double.parseDouble(timeFormatter.format(r
				.nextDouble()
				* (midval - min) + min));
		double secondval = Double.parseDouble(timeFormatter.format(r
				.nextDouble()
				* (max - midval) + midval));

		double[] result = { firstval, secondval };
		return result;
	}

	private double[] generateHalfInterval(int min, int max) {
		double midval = (max + min) / 2;
		Random r = new Random();
		double secondval = Double.parseDouble(timeFormatter.format(r
				.nextDouble()
				* (max - midval) + midval));

		double[] result = { min, secondval };
		return result;
	}
	// public static void main(String[] args) throws Exception {
	// QueryGen gen = new QueryGen();
	// gen.generatev1("d:/temp/genqueries.txt");
	// double tes = 123948.83829023234;
	// System.out.println(formatter.format(tes));
	// }
}
